﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Xml;
using System.Data.OleDb;
using System.Windows.Forms;

namespace ResxManager
{
	class Excel
	{
		#region constants
		// 확장명 XLS (Excel 97~2003 용)
		private const string ConnectStrFrm_Excel97_2003 =
			"Provider=Microsoft.Jet.OLEDB.4.0;" +
			"Data Source=\"{0}\";" +
			"Mode=ReadWrite|Share Deny None;" +
			"Extended Properties='Excel 8.0; HDR={1}; IMEX={2}';" +
			"Persist Security Info=False";

		// 확장명 XLSX (Excel 2007 이상용)
		private const string ConnectStrFrm_Excel =
			"Provider=Microsoft.ACE.OLEDB.12.0;" +
			"Data Source=\"{0}\";" +
			"Mode=ReadWrite|Share Deny None;" +
			"Extended Properties='Excel 12.0; HDR={1}; IMEX={2}';" +
			"Persist Security Info=False";
		#endregion

		public bool SaveExcel(string fileName, DataSet ds, bool existDel, bool oldExcel)
		{
			bool result = true;

			if (File.Exists(fileName))
				if (existDel)
					File.Delete(fileName);
				else
					return result;

			string tempFile = fileName;

			OleDbConnection oleDBConn = null;

			try
			{
				OleDbCommand cmd = null;
				string connStr = "";

				if (oldExcel)
				{
					tempFile = tempFile + ".xls";
					connStr = string.Format(ConnectStrFrm_Excel97_2003, tempFile, "YES", "0");
				}
				else
				{
					tempFile = tempFile + ".xlsx";
					connStr = string.Format(ConnectStrFrm_Excel, tempFile, "YES", "0");
				}

				oleDBConn = new OleDbConnection(connStr);
				oleDBConn.Open();

				foreach (DataTable dt in ds.Tables)
				{
					string tableName = dt.TableName;

					StringBuilder fldsInfo = new StringBuilder();
					StringBuilder flds = new StringBuilder();

					foreach (DataColumn c in dt.Columns)
					{
						if (fldsInfo.Length > 0)
						{
							fldsInfo.Append(",");
							flds.Append(",");
						}

						fldsInfo.Append("[" + c.ColumnName.Replace("'", "''") + "] MEMO");
						flds.Append("[" + c.ColumnName.Replace("'", "''") + "]");
					}

					cmd = new OleDbCommand("CREATE TABLE [" + tableName + "](" + fldsInfo.ToString() + ")", oleDBConn);
					cmd.ExecuteNonQuery();

					foreach (DataRow dr in dt.Rows)
					{
						StringBuilder values = new StringBuilder();
						foreach (DataColumn c in dt.Columns)
						{
							if (values.Length > 0)
								values.Append(",");
							values.Append("'" + dr[c.ColumnName].ToString().Replace("'", "''") + "'");
						}

						cmd = new OleDbCommand(
							"INSERT INTO [" + tableName + "$]" +
							"(" + flds.ToString() + ") " +
							"VALUES (" + values.ToString() + ")",
							oleDBConn);
						cmd.ExecuteNonQuery();
					}

				}
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
				result = false;
			}
			finally
			{
				if (oleDBConn != null)
					oleDBConn.Close();
				try
				{
					if (File.Exists(tempFile))
						File.Move(tempFile, fileName);
				}
				catch { }
			}
			return result;
		}

		public DataSet OpenExcel(string fileName, bool useHeader)
		{
			DataSet ds = null;

			string[] hdropt = { "NO", "YES" };
			string hdr = "";
			string connStr = "";

			if (useHeader)
				hdr = hdropt[1];
			else
				hdr = hdropt[0];

			int excelType = ExcelFileType(fileName);

			switch (excelType)
			{
				case -2:
					throw new Exception(fileName + "의 형식검사 중 오류가 발생하였습니다.");
				case -1:
					throw new Exception(fileName + "은 엑셀 파일 형식이 아닙니다.");
				case 0:
					connStr = string.Format(ConnectStrFrm_Excel97_2003, fileName, hdr, "1");
					break;
				case 1:
					connStr = string.Format(ConnectStrFrm_Excel, fileName, hdr, "1");
					break;
			}

			OleDbConnection oleDBConn = null;
			OleDbDataAdapter oleDBAdapt = null;
			DataTable schema;

			try
			{
				oleDBConn = new OleDbConnection(connStr);
				oleDBConn.Open();

				schema = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

				ds = new DataSet();

				foreach (DataRow dr in schema.Rows)
				{
					oleDBAdapt = new OleDbDataAdapter(dr["TABLE_NAME"].ToString(), oleDBConn);

					oleDBAdapt.SelectCommand.CommandType = CommandType.TableDirect;
					oleDBAdapt.AcceptChangesDuringFill = false;

					string tableName = dr["TABLE_NAME"].ToString().Replace("$", string.Empty).Replace("'", string.Empty);

					if (dr["TABLE_NAME"].ToString().Contains("$"))
						oleDBAdapt.Fill(ds, tableName);
				}
			}
			catch (System.Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (oleDBConn != null)
					oleDBConn.Close();
			}
			return ds;
		}

		public static int ExcelFileType(string XlsFile)
		{
			byte[,] ExcelHeader = {
				{ 0xD0, 0xCF, 0x11, 0xE0, 0xA1 }, // XLS  File Header
                { 0x50, 0x4B, 0x03, 0x04, 0x14 }  // XLSX File Header
			};

			//-2 = error
			//-1 = not excel
			// 0 = xls
			// 1 = xlsx
			int result = -1;

			FileInfo fi = new FileInfo(XlsFile);
			FileStream fs = fi.Open(FileMode.Open);

			try
			{
				byte[] fh = new byte[5];

				fs.Read(fh, 0, 5);
				for (int i = 0; i < 2; i++)
				{
					for (int j = 0; j < 5; j++)
					{
						if (fh[j] != ExcelHeader[i, j])
							break;
						else if (j == 4)
							result = i;
						if (result > 0)
							break;
					}
				}
			}
			catch
			{
				result = -2;
			}
			finally
			{
				fs.Close();
			}
			return result;
		}
	}
}
